2.2 PostgreSQL MVCC

1. General Rules

• In PostgreSQL, every transaction gets a transaction ID called XID.
  • It can be queried with select cast(txid_current() as text).
  • The transactions mentioned here are not only groups of statements wrapped by BEGIN - COMMIT, but also individual insert, update, or delete statements.
• When a transaction begins, PostgreSQL increments XID and assigns it to the transaction.

2026 note: this is a simplified way to understand it. PostgreSQL transaction IDs may be assigned lazily only when they are actually needed; row visibility also cannot be judged only by XID magnitude, but must be determined together with the snapshot’s xmin / xmax / active-transaction set and transaction commit status. The old txid_* query interfaces are currently mainly retained for compatibility.

• PostgreSQL also stores transaction-related information on every row record in the system, which is used to determine whether a row record is visible to the current transaction.

See the example in the following figure:

Note that the above explanation applies to committed transactions. Uncommitted transactions are invisible to other transactions in any case.

2. Examples

2.1. Insert

• If the transaction ID is greater than the xmin of a committed row, the transaction can see that row.
• If the transaction ID is smaller than the xmin of a committed row, whether it can see the row depends on the isolation level.
  • At RC level, the current time is taken when the currently executing SQL statement runs, so the row can be seen.
  • At RR/S level, the current time is taken from the beginning of the currently running transaction, so the row cannot be seen.

2.2. Delete

• If the transaction ID is greater than the xmax of a committed row, the transaction cannot see the deleted row (that is, it can see the deletion).
• If the transaction ID is smaller than the xmax of a committed row, whether it can see the row depends on the isolation level.
  • At RC level, the current time is taken when the currently executing SQL statement runs, so the transaction cannot see the deleted row (that is, it can see the deletion).
  • At RR/S level, the current time is taken from the beginning of the currently running transaction, so the row can still be seen.

2.3. Update

• An update is essentially delete + insert. The xmax of the deleted row is set to the current transaction ID, and the xmin of the newly inserted row is set to the current transaction ID.
• Whether the deleted row can be seen follows the Delete rules above.
• Whether the inserted row can be seen follows the Insert rules above.

3. Underlying Principle

3.1. Define Multi-Version Data

Use the xmin and xmax fields in HeapTupleHeaderData to identify tuple versions.

• xmin represents the transaction ID when the tuple was inserted.
• xmax represents the transaction ID when the tuple was updated/deleted.

3.2. Define a Transaction Snapshot

A snapshot is a set of values at a certain moment for which the xmin and xmax of all tuples in the database satisfy certain conditions. SnapshotData also has two fields, xmin and xmax, defined as follows:

• Check all uncommitted and active transactions at the current moment, store them in an array, select the smallest XID, and record it in the snapshot’s xmin.
• Select the largest XID among all committed transactions, add 1, and record it in xmax.

3.3. Define Snapshot Timing

Snapshot timing is determined by the isolation level defined by the user. The three transaction isolation levels take snapshots at different times, but their judgment conditions are the same.

• If xmin equals the current transaction ID, include all tuples with xmax=0 (not deleted).
• If the transaction corresponding to the transaction ID equal to xmin has already committed, include all tuples with xmax=0 or xmax equal to the current transaction ID.